package me.zhengjie.modules.system.repository

import me.zhengjie.modules.system.domain.User
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.JpaSpecificationExecutor
import org.springframework.data.jpa.repository.Modifying
import org.springframework.data.jpa.repository.Query
import java.util.*


/**
 * @author Kuki Wu
 * @date 2024-11-22
 */
interface UserRepository : JpaRepository<User?, Long?>, JpaSpecificationExecutor<User?> {
    fun findByUsername(username: String?): User?
    fun findByEmail(email: String?): User?

    /**
     * 根据手机号查询
     * @param phone 手机号
     * @return /
     */
    fun findByPhone(phone: String?): User?


    @Modifying
    @Query(
        value = "update sys_user set password = ?2 , last_password_reset_time = ?3 where username = ?1",
        nativeQuery = true
    )
    fun updatePass(username: String?, pass: String?, lastPasswordResetTime: Date?)

    @Modifying
    @Query(value = "update sys_user set email = ?2 where username = ?1", nativeQuery = true)
    fun updateEmail(username: String?, email: String?)

    /**
     * 根据角色查询用户
     * @param roleId /
     * @return /
     */
    @Query(
        value = "SELECT u.* FROM sys_user u, sys_users_roles r WHERE" +
                " u.user_id = r.user_id AND r.role_id = ?1", nativeQuery = true
    )
    fun findByRoleId(roleId: Long?): List<User?>?

    /**
     * 根据角色中的部门查询
     * @param deptId /
     * @return /
     */
    @Query(
        value = "SELECT u.* FROM sys_user u, sys_users_roles r, sys_roles_depts d WHERE " +
                "u.user_id = r.user_id AND r.role_id = d.role_id AND d.dept_id = ?1 group by u.user_id",
        nativeQuery = true
    )
    fun findByRoleDeptId(deptId: Long?): List<User?>?

    /**
     * 根据菜单查询
     * @param id 菜单ID
     * @return /
     */
    @Query(
        value = "SELECT u.* FROM sys_user u, sys_users_roles ur, sys_roles_menus rm WHERE u.user_id = ur.user_id AND ur.role_id = rm.role_id AND rm.menu_id = ?1 group by u.user_id", nativeQuery = true
    )
    fun findByMenuId(id: Long?): List<User?>?

    /**
     * 根据Id删除
     * @param ids /
     */
    fun deleteAllByIdIn(ids: Set<Long?>?)

    /**
     * 根据岗位查询
     * @param ids /
     * @return /
     */
    @Query(
        value = "SELECT count(1) FROM sys_user u, sys_users_jobs j WHERE u.user_id = j.user_id AND j.job_id IN ?1",
        nativeQuery = true
    )
    fun countByJobs(ids: Set<Long?>?): Int

    /**
     * 根据部门查询
     * @param deptIds /
     * @return /
     */
    @Query(value = "SELECT count(1) FROM sys_user u WHERE u.dept_id IN ?1", nativeQuery = true)
    fun countByDepts(deptIds: Set<Long?>?): Int

    /**
     * 根据角色查询
     * @param ids /
     * @return /
     */
    @Query(
        value = "SELECT count(1) FROM sys_user u, sys_users_roles r WHERE " +
                "u.user_id = r.user_id AND r.role_id in ?1", nativeQuery = true
    )
    fun countByRoles(ids: Set<Long?>?): Int

    /**
     * 重置密码
     * @param ids 、
     * @param pwd 、
     */
    @Modifying
    @Query(value = "update sys_user set password = ?2 where user_id in ?1", nativeQuery = true)
    fun resetPwd(ids: Set<Long?>?, pwd: String?)
}